package tm.data;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import tm.dto.Details;
import tm.dto.Taskdetails;
import tm.dto.TaskdetailsId;
import tm.dto.Tasks;
import tm.dto.Users;

public class TaskManagerJDBCDao extends MySQLJDBCGenericDao {

	private static TaskManagerJDBCDao me = null;
	
	private static DetailsDao detailsDao = DaoFactory.getInstance().getDetailsDao();
	
	private static String getProjectIdSQL = "SELECT DISTINCT p.PROJECTID FROM" +
	 " assignments a, tasks t, projectsmilestones pm, projects p" +
	 " where userid=?" +
	 " and a.taskid = t.taskid" +
	 " and t.milestoneid = pm.milestoneid" +
	 " and pm.projectid = p.projectid";
	
	private static String getTaskIdSQL = "SELECT DISTINCT a.TASKID FROM" +
	 " assignments a, tasks t, projectsmilestones pm, projects p" +
	 " where userid=? and p.projectid=?" +
	 " and a.taskid = t.taskid" +
	 " and t.milestoneid = pm.milestoneid" +
	 " and pm.projectid = p.projectid";
	
	private static String getTaskDetailIdSQL = "SELECT DISTINCT d.detailid FROM" +
	 " taskdetails td, tasks t, details d, projectsmilestones pm" +
	 " where userid=? and td.taskid=? and projectid=?" +
	 " and td.detailid=d.detailid" +
	 " and td.taskid=t.taskid" +
	 " and t.milestoneid=pm.milestoneid";
	
	private static String updateDetailSQL = "UPDATE details" +
	" set transactionTimestamp=?, startTimestamp=?, timeAmount=?, comment=?, catId=?, taskStateId=?" +
	" where detailId=?";
	
	private static String insertDetailSQL = "INSERT into details" +
	" (transactionTimestamp, startTimestamp, timeAmount, comment, catId, taskStateId, detailid)" +
	" values(?,?,?,?,?,?,?)";
	
	private static String insertTaskDetailSQL = "INSERT into taskdetails" + 
	" values(?,?,?)";
	
	private static String deleteDetailSQL = "DELETE from details" +
	" where detailid=?";
	
	private static String deleteTaskDetailSQL = "DELETE from taskdetails" + 
	" where userId=?" +
	" and taskId=?" +
	" and detailid=?";
	
	
	private TaskManagerJDBCDao(){
		super();
	}
	
	/** Intent: instantiate TaskManagerJDBCDao object if it does not exist. 
	 * 
	 *  Singleton pattern.
	 * 
	 * @return TaskManagerJDBCDao
	 */
	public static TaskManagerJDBCDao getInstance(){
		if (me == null)
			me = new TaskManagerJDBCDao();
		return me;
	}
	
	/** Intent: Supporting method for TaskDetailOperations deleteTaskDetails method.
	 * 
	 * Precondition: Users, Tasks and Details must exist in Database in order to delete the task details.
	 * 
	 * @param Users object
	 * @param Tasks object
	 * @param Details object
	 * @return Nothing.
	 * 
	 * PostCondition: Task Detail for User working on Task deleted from Database.
	 */
	public void deleteTaskDetails(Users user, Tasks task, Details detail){
		try {
			PreparedStatement ps = getConn().prepareStatement(deleteTaskDetailSQL);		
			ps.clearParameters();
			ps.setInt(1, user.getUserId());
			ps.setInt(2, task.getTaskId());
			ps.setInt(3, detail.getDetailId());
			ps.execute();
			
			ps = getConn().prepareStatement(deleteDetailSQL);
			ps.clearParameters();
			ps.setInt(1, detail.getDetailId());
			ps.execute();
			
		}catch (SQLException se){
	    	System.out.println("SQL Exception thrown..");
	    }
	}
	
	/** Intent: Supporting method for TaskDetailOperations updateTaskDetails method.
	 * 
	 * Precondition: Users, Tasks and Details must exist in Database in order to update the task details.
	 * 
	 * @param Users object
	 * @param Tasks object
	 * @param Details object
	 * @return Nothing.
	 * 
	 * PostCondition: Task Detail for User working on Task updated in Database.
	 */
	public void updateTaskDetails(Details detail){
		try {
			PreparedStatement ps = getConn().prepareStatement(updateDetailSQL);
			ps.clearParameters();
			
			Timestamp tsTrans = new Timestamp(detail.getTransactionTimestamp().getTime());
			//System.out.println("Transaction TimeStamp: "+tsTrans);
			ps.setString(1, tsTrans.toString());
			
			//ps.setTimestamp(1, new Timestamp(detail.getTransactionTimestamp().getTime()));
			Timestamp tsStart = new Timestamp(detail.getStartTimestamp().getTime());
			//System.out.println("Transaction TimeStamp: "+tsStart);
			ps.setString(2, tsStart.toString());
			
			ps.setInt(3, detail.getTimeAmount());
			ps.setString(4, detail.getComment());
			ps.setInt(5, detail.getCategories().getCatId());
			ps.setInt(6, detail.getTaskstates().getTaskStateId());
			ps.setInt(7, detail.getDetailId());
			
			//System.out.println("PS "+ps.toString());
			ps.executeUpdate();
			
		}catch (SQLException se){
	    	System.out.println("SQL Exception thrown..");
	    }
	}
	
	/** Intent: Supporting method for TaskDetailOperations insertTaskDetails method.
	 * 
	 * Precondition #1: Users, Tasks must exist in Database in order to insert new task details.
	 * Precondition #2: Detail must not exist for given User and Task.
	 * 
	 * @param Users object
	 * @param Tasks object
	 * @param Details object
	 * @return Nothing.
	 * 
	 * PostCondition: New Task Detail for User working on Task inserted in Database.
	 */
	public void insertTaskDetails(Users user, Tasks task, Details detail){
		try {
			PreparedStatement ps = getConn().prepareStatement(insertDetailSQL);
			ps.clearParameters();
			
			Timestamp tsTrans = new Timestamp(detail.getTransactionTimestamp().getTime());
			//System.out.println("Transaction TimeStamp: "+tsTrans);
			ps.setString(1, tsTrans.toString());
			
			//ps.setTimestamp(1, new Timestamp(detail.getTransactionTimestamp().getTime()));
			Timestamp tsStart = new Timestamp(detail.getStartTimestamp().getTime());
			//System.out.println("Transaction TimeStamp: "+tsStart);
			ps.setString(2, tsStart.toString());
			
			ps.setInt(3, detail.getTimeAmount());
			ps.setString(4, detail.getComment());
			ps.setInt(5, detail.getCategories().getCatId());
			ps.setInt(6, detail.getTaskstates().getTaskStateId());
			ps.setInt(7, detail.getDetailId());
			
			//System.out.println("PS "+ps.toString());
			ps.executeUpdate();
			ps = getConn().prepareStatement(insertTaskDetailSQL);
			ps.clearParameters();
			ps.setInt(1, user.getUserId());
			ps.setInt(2, task.getTaskId());
			ps.setInt(3, detail.getDetailId());
			ps.execute();
			
		}catch (SQLException se){
	    	System.out.println("SQL Exception thrown..");
	    }
	}
	/** Intent: Supporting method for TaskDetailOperations getProjects method.
	 * 
	 * Precondition: None
	 * 
	 * @param User ID 
	 * @return List of Projects objects the user is working on.
	 * 
	 * PostCondition: None.
	 */
	public List projectsDBQuery(int userId){
		List projectIds = new ArrayList();
		try {
			PreparedStatement ps = getConn().prepareStatement(getProjectIdSQL);
			ps.clearParameters();
			ps.setInt(1, userId);
			//System.out.println("PS "+ps.toString());
			ResultSet rs = ps.executeQuery();
			while (rs.next()){
				Integer posId = rs.getInt(1);
				projectIds.add(posId);
			}
			
		}catch (SQLException se){
	    	System.out.println("SQL Exception thrown..");
	    }
		return projectIds;
	}
	
	/** Intent: Supporting method for TaskDetailOperations getTasksProjects method.
	 * 
	 * Precondition: None
	 * 
	 * @param User ID
	 * @param Project ID 
	 * @return List of Tasks objects the user is working on for a given project.
	 * 
	 * PostCondition: None.
	 */
	public List taskDBQuery(int userId, int projId){
		List taskIds = new ArrayList();
		try {
			PreparedStatement ps = getConn().prepareStatement(getTaskIdSQL);
			ps.clearParameters();
			ps.setInt(1, userId);
			ps.setInt(2, projId);
			//System.out.println("PS "+ps.toString());
			ResultSet rs = ps.executeQuery();
			while (rs.next()){
				Integer task = rs.getInt(1);
				taskIds.add(task);
			}
			
		}catch (SQLException se){
	    	System.out.println("SQL Exception thrown..");
	    }
		return taskIds;
	}
	
	/** Intent: Supporting method for TaskDetailOperations getTaskdetails method.
	 * 
	 * Precondition: None
	 * 
	 * @param User ID
	 * @param Project ID 
	 * @param Task ID
	 * @return List of Task Details objects the user is working on for a given project for a given Task ID.
	 * 
	 * PostCondition: None.
	 */
	public List taskDetailsQuery(int userId, int projId, int taskId){
		
		List taskDetailIds = new ArrayList();

		try {					
			TaskdetailsId tdid = new TaskdetailsId();
			tdid.setTaskId(taskId);
			tdid.setUserId(userId);
			PreparedStatement ps = getConn().prepareStatement(getTaskDetailIdSQL);
			ps.clearParameters();
			ps.setInt(1, userId);
			ps.setInt(2, taskId);
			ps.setInt(3, projId);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				Taskdetails td = new Taskdetails();
				int taskDetailId = rs.getInt(1);
				tdid.setDetailId(taskDetailId);
				Details dt = (Details)detailsDao.findById(taskDetailId);
				td.setId(tdid);
				td.setDetails(dt);
				taskDetailIds.add(td);
			}
		}catch (SQLException se){
	    	System.out.println("SQL Exception thrown..");
	    }
		return taskDetailIds;
		
	}

}
